package com.hx.controller;


import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.hx.service.TestService;
import com.hx.utils.*;


import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.util.*;

/**
 * @Auther: QZ
 * @Date: 2022/6/22 - 06 - 22 - {TIME}
 * @version: 1.0
 */
@Api(tags = "sql管理")
@RestController
@RequestMapping("/auto")
public class AutoTableController {
    @Autowired
    private TestService service;
    @Autowired
    private CreateTableEnd createTableEnd;
    @Autowired
    private excelReadEnd excelReadEnd;

    @Value("${datasource.name}")
    private String datasourceNmae;

    @ApiOperation("根据前台请求的字段，生成数据库表格")
    @PostMapping("/createTable")
    //@RequestMapping("/createTable")
    public R createTable(@RequestParam(value = "cols",required = false) String col,
                         @RequestParam(value = "tableName",required =false) String tableName) throws Exception{
        if(col.isEmpty()||tableName.isEmpty()){
            return R.error().message("请输入表名或者字段名");
        }
        String[] cols = {};
        if(col.contains(",")){
            cols = col.split(",");
        }
        if(col.contains("，")) {
            cols = col.split("，");
        }

        ArrayList list = new ArrayList<>();
        for (String s : cols) {
            list.add("class java.lang.String");
        }
        //String arr = StringUtils.join(list,','); //把list转成string类型

        String test = String.join(",",list);
        //System.out.println(arr);
        String[] type = (String[]) list.toArray(new String[list.size()]);//把list转成String数组格式
        //CreateTableEnd con = new CreateTableEnd();

        String sql = createTableEnd.createTablesql(tableName, cols, type);
        System.out.println(sql);
        String isflag = createTableEnd.createTable(sql, tableName);
        //String msg = createTableEnd.createTableUtils(cols, type, tableName);
        return R.ok().message(isflag);
    }
    /**
     * 导入数据到数据库表格中
     */

    @ApiOperation(value = "Excel批量导入")
    @PostMapping("addAutoData")
    public R addAutoData(@RequestParam("file") MultipartFile file,@RequestParam("tablename") String tablename) throws Exception {
        if(tablename==null){
            return R.error().message("请输入表名");
        }
        //System.out.println(tablename);
        String filename = file.getOriginalFilename();
        //System.out.println(filename);
        //判断导入的文件名和表名是否相同
        if ((filename != null) && (filename.length() > 0)) {
            int dot = filename.lastIndexOf('.');
            if ((dot >-1) && (dot < (filename.length()))) {
                filename = filename.substring(0, dot);
                if(!filename.equals(tablename)){
                    return R.error().message("导入的文件名与表名不一致");
                }
            }
        }
        File fileEnd = MultipartFileToFile.multipartFileToFile(file);
        //1 获取上传的excel文件 MultipartFile
        /*excelRead excelRead = new excelRead();
        int i = excelRead.readExcel(tablename, fileEnd);// 传入要操作的excel路径*/
        //excelReadEnd excelReadEnd = new excelReadEnd();
        int i = excelReadEnd.readExcel(tablename, fileEnd);
        System.out.println(i);
        if(i==0){
            return R.ok().message("导入数据成功");
        }
        return R.error().message("error,接口异常");
    }

    /**
     * 查询指定表名的数据
     */
    @ApiOperation(value = "查询指定数据表中的数据")
    @PostMapping("/getAllData")
    public JSONObject getAllData( String tableName,
                        @RequestParam(value = "page",required = true,defaultValue = "1") Integer page,
                        @RequestParam(value = "limit",required = true,defaultValue = "10") Integer limit){
       /* if(tableName.isEmpty()){
            return R.error().message("表名不能为空");
        }*/
        System.out.println(tableName);
        PageHelper.startPage(page,limit);
        //tableName = "   "+tableName;
        //List<Map<String, String>> data = service.getAllData(tableName,page,limit);
        List<Map<String, String>> data = service.getAllData(tableName);
        PageInfo pageInfo = new PageInfo(data);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","成功");
        jsonObject.put("count",pageInfo.getTotal());
        jsonObject.put("data",data);
        return jsonObject;
    }

    @ApiOperation(value = "关联查询所有数据")
    @PostMapping("/getAssociatedQueryData")
    public JSONObject getAssociatedQueryData(@RequestParam("tableNameOne") String tableNameOne,
                                                  @RequestParam("tableNameTwo") String tableNameTwo,
                                                  @RequestParam("fieldOne") String fieldOne,
                                                  @RequestParam("fieldTwo") String fieldTwo,
                                                  @RequestParam(value = "page",required = true,defaultValue = "1") Integer page,
                                                  @RequestParam(value = "limit",required = true,defaultValue = "10") Integer limit
    ){
        PageHelper.startPage(page,limit);
        List associatedQueryDataList = service.getAssociatedQueryData(tableNameOne, tableNameTwo, fieldOne, fieldTwo);
        associatedQueryDataList.forEach(System.out::println);
        PageInfo pageInfo = new PageInfo(associatedQueryDataList);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","成功");
        jsonObject.put("count",pageInfo.getTotal());
        jsonObject.put("data",associatedQueryDataList);
        return jsonObject;
    }



    @ApiOperation(value = "关联查询右连接")
    @PostMapping("/getAssociatedQueryDataRight")
    public JSONObject getAssociatedQueryDataRight(@RequestParam("tableNameOne") String tableNameOne,
                                             @RequestParam("tableNameTwo") String tableNameTwo,
                                             @RequestParam("fieldOne") String fieldOne,
                                             @RequestParam("fieldTwo") String fieldTwo,
                                             @RequestParam(value = "page",required = true,defaultValue = "1") Integer page,
                                             @RequestParam(value = "limit",required = true,defaultValue = "10") Integer limit
    ){
        PageHelper.startPage(page,limit);
        List associatedQueryDataListRight = service.getAssociatedQueryDataRight(tableNameOne, tableNameTwo, fieldOne, fieldTwo);
        associatedQueryDataListRight.forEach(System.out::println);
        PageInfo pageInfo = new PageInfo(associatedQueryDataListRight);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","成功");
        jsonObject.put("count",pageInfo.getTotal());
        jsonObject.put("data",associatedQueryDataListRight);
        return jsonObject;
    }

    /**
     * 支持多张表进行查询 左连接查询数据
     * @return
     */
    @ApiOperation(value = "关联查询左连接")
    @PostMapping("/getAssociatedQueryDataLeft")
    public JSONObject getAssociatedQueryDataLeft(@RequestParam("tableNameOne") String tableNameOne,
                                    @RequestParam("tableNameTwo") String tableNameTwo,
                                    @RequestParam("fieldOne") String fieldOne,
                                    @RequestParam("fieldTwo") String fieldTwo,
                                    @RequestParam(value = "page",required = true,defaultValue = "1") Integer page,
                                    @RequestParam(value = "limit",required = true,defaultValue = "4") Integer limit
                                    ){
      /*  System.out.println(tableNameOne);
        System.out.println(tableNameTwo);
        System.out.println(fieldOne);
        System.out.println(fieldTwo);*/

        PageHelper.startPage(page,limit);
        List associatedQueryDataList = service.getAssociatedQueryDataLeft(tableNameOne, tableNameTwo, fieldOne, fieldTwo);
        associatedQueryDataList.forEach(System.out::println);
       /* List associatedQueryDataListRight = service.getAssociatedQueryDataRight(tableNameOne, tableNameTwo, fieldOne, fieldTwo);
        associatedQueryDataListRight.forEach(System.out::println);
       *//* ArrayList list = new ArrayList<>();
        list.addAll(associatedQueryDataList);
        list.addAll(associatedQueryDataListRight);
        list.forEach(System.out::println);*/
        PageInfo pageInfo = new PageInfo(associatedQueryDataList);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","成功");
        jsonObject.put("count",pageInfo.getTotal());
        jsonObject.put("data",associatedQueryDataList);
        return jsonObject;
    }

    /**
     * 查询数据库所有的表
     */
    @ApiOperation(value = "查询数据库所有的表")
    @PostMapping("/getAllTableName")
    public R getAllTableName(){

       String[] tables = service.getAllTableName();

        return R.ok().data("table",tables);
    }
    /**
     * 根据表名查询表名下的所有字段
     */
    @ApiOperation(value = "根据表名查询表名下的所有字段")
    @PostMapping("/getAllTableFiled")
    public R getAllTableFiled(@RequestParam("tableName") String tableName){
        //System.out.println(tableName);
        String[] tableFileds = service.getAllTableFiled(tableName);
        return R.ok().data("tableFiled",tableFileds);
    }

    /**
     * 删除表
     */
    @ApiOperation(value = "批量删除指定的表")
    @PostMapping("/deleteTable")
    public R deleteByTableNames(@RequestParam("tableName") String tableName){
        System.out.println(tableName);
        service.deleteByTableNames(tableName);
        return R.ok().message("删除表成功");
    }

    /**
     * 删除指定表中的数据
     */
    @ApiOperation(value = "删除指定表中的数据")
    @PostMapping("/removeTableData")
    public R removeDataBytableName(@RequestParam("tableName") String tableName){
        System.out.println(tableName);
        service.removeDataBytableName(tableName);
        return R.ok().message("清空表数据成功");
    }

    /**
     * 得到所有表格名和数据条数
     */
    @ApiOperation(value = "得到所有表格名和数据条数")
    @PostMapping("/getTableData")
    public JSONObject getAllTableData(@RequestParam(value = "page",required = true,defaultValue = "1") Integer page,
                                      @RequestParam(value = "limit",required = true,defaultValue = "5") Integer limit){
        System.out.println(page+""+limit);
        PageHelper.startPage(page,limit);
        System.out.println(datasourceNmae);
        List tableList = service.getTableData(datasourceNmae);
        PageInfo pageInfo = new PageInfo(tableList);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","成功");
        jsonObject.put("count",pageInfo.getTotal());
        jsonObject.put("data",tableList);
        return jsonObject;
    }

}
